# -*- coding: utf-8 -*-
"""
Pandas 从基础到高级的完整示例

本文件包含从浅入深的Pandas使用示例，涵盖基础操作到高级应用，
包括真实数据集的处理和分析。
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

# ============================================================================
# 第一部分：基础操作 (Beginner Level)
# ============================================================================

def basic_pandas_operations():
    """基础Pandas操作示例"""
    print("=" * 60)
    print("第一部分：基础Pandas操作")
    print("=" * 60)
    
    # 1.1 创建Series和DataFrame
    print("\n1.1 创建Series和DataFrame:")
    
    # 创建Series
    s1 = pd.Series([1, 3, 5, np.nan, 6, 8])
    s2 = pd.Series([10, 20, 30, 40], index=['a', 'b', 'c', 'd'])
    
    print(f"Series 1:\n{s1}")
    print(f"\nSeries 2 (带索引):\n{s2}")
    
    # 创建DataFrame的多种方式
    # 方式1：从字典创建
    data_dict = {
        'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],
        'Age': [25, 30, 35, 28],
        'City': ['New York', 'London', 'Tokyo', 'Paris'],
        'Salary': [50000, 60000, 70000, 55000]
    }
    df1 = pd.DataFrame(data_dict)
    print(f"\n从字典创建DataFrame:\n{df1}")
    
    # 方式2：从列表创建
    data_list = [
        ['Alice', 25, 'New York', 50000],
        ['Bob', 30, 'London', 60000],
        ['Charlie', 35, 'Tokyo', 70000],
        ['Diana', 28, 'Paris', 55000]
    ]
    df2 = pd.DataFrame(data_list, columns=['Name', 'Age', 'City', 'Salary'])
    print(f"\n从列表创建DataFrame:\n{df2}")
    
    # 1.2 基本信息查看
    print("\n1.2 基本信息查看:")
    print(f"DataFrame形状: {df1.shape}")
    print(f"列名: {list(df1.columns)}")
    print(f"索引: {list(df1.index)}")
    print(f"数据类型:\n{df1.dtypes}")
    print(f"\n基本统计信息:\n{df1.describe()}")
    
    # 1.3 数据选择和索引
    print("\n1.3 数据选择和索引:")
    
    # 选择列
    print(f"选择Name列:\n{df1['Name']}")
    print(f"\n选择多列:\n{df1[['Name', 'Age']]}")
    
    # 选择行
    print(f"\n选择第一行:\n{df1.iloc[0]}")
    print(f"\n选择前两行:\n{df1.head(2)}")
    
    # 条件选择
    high_salary = df1[df1['Salary'] > 55000]
    print(f"\n薪资大于55000的员工:\n{high_salary}")

# ============================================================================
# 第二部分：中级操作 (Intermediate Level)
# ============================================================================

def intermediate_pandas_operations():
    """中级Pandas操作示例"""
    print("\n" + "=" * 60)
    print("第二部分：中级Pandas操作")
    print("=" * 60)
    
    # 2.1 数据清洗和处理
    print("\n2.1 数据清洗和处理:")
    
    # 创建包含缺失值的数据
    data_with_missing = {
        'A': [1, 2, np.nan, 4, 5],
        'B': [np.nan, 2, 3, 4, 5],
        'C': [1, 2, 3, np.nan, 5],
        'D': [1, 2, 3, 4, 5]
    }
    df_missing = pd.DataFrame(data_with_missing)
    print(f"包含缺失值的DataFrame:\n{df_missing}")
    
    # 检查缺失值
    print(f"\n缺失值统计:\n{df_missing.isnull().sum()}")
    
    # 处理缺失值
    df_dropped = df_missing.dropna()  # 删除含缺失值的行
    df_filled = df_missing.fillna(0)  # 用0填充缺失值
    df_forward_fill = df_missing.fillna(method='ffill')  # 前向填充
    
    print(f"\n删除缺失值后:\n{df_dropped}")
    print(f"\n用0填充缺失值后:\n{df_filled}")
    print(f"\n前向填充后:\n{df_forward_fill}")
    
    # 2.2 数据变换和操作
    print("\n2.2 数据变换和操作:")
    
    # 创建示例数据
    sales_data = {
        'Product': ['A', 'B', 'A', 'C', 'B', 'A', 'C'],
        'Region': ['North', 'South', 'North', 'East', 'South', 'West', 'East'],
        'Sales': [100, 150, 120, 200, 180, 110, 190],
        'Quantity': [10, 15, 12, 20, 18, 11, 19]
    }
    df_sales = pd.DataFrame(sales_data)
    print(f"销售数据:\n{df_sales}")
    
    # 分组聚合
    product_summary = df_sales.groupby('Product').agg({
        'Sales': ['sum', 'mean', 'count'],
        'Quantity': 'sum'
    })
    print(f"\n按产品分组聚合:\n{product_summary}")
    
    # 透视表
    pivot_table = df_sales.pivot_table(
        values='Sales', 
        index='Product', 
        columns='Region', 
        aggfunc='sum', 
        fill_value=0
    )
    print(f"\n透视表:\n{pivot_table}")
    
    # 2.3 时间序列处理
    print("\n2.3 时间序列处理:")
    
    # 创建时间序列数据
    dates = pd.date_range('2023-01-01', periods=30, freq='D')
    ts_data = pd.DataFrame({
        'Date': dates,
        'Value': np.random.randn(30).cumsum() + 100
    })
    ts_data.set_index('Date', inplace=True)
    
    print(f"时间序列数据 (前5行):\n{ts_data.head()}")
    
    # 时间序列操作
    print(f"\n2023年1月的数据:\n{ts_data['2023-01']}")
    
    # 重采样
    weekly_data = ts_data.resample('W').mean()
    print(f"\n按周重采样 (平均值):\n{weekly_data}")

# ============================================================================
# 第三部分：高级操作 (Advanced Level)
# ============================================================================

def advanced_pandas_operations():
    """高级Pandas操作示例"""
    print("\n" + "=" * 60)
    print("第三部分：高级Pandas操作")
    print("=" * 60)
    
    # 3.1 多层索引 (MultiIndex)
    print("\n3.1 多层索引 (MultiIndex):")
    
    # 创建多层索引数据
    arrays = [
        ['A', 'A', 'B', 'B'],
        ['one', 'two', 'one', 'two']
    ]
    tuples = list(zip(*arrays))
    index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
    
    df_multi = pd.DataFrame(np.random.randn(4, 2), index=index, columns=['X', 'Y'])
    print(f"多层索引DataFrame:\n{df_multi}")
    
    # 多层索引操作
    print(f"\n选择'A'组数据:\n{df_multi.loc['A']}")
    print(f"\n选择('A', 'one')数据:\n{df_multi.loc[('A', 'one')]}")
    
    # 3.2 数据合并和连接
    print("\n3.2 数据合并和连接:")
    
    # 创建示例数据
    df_left = pd.DataFrame({
        'key': ['A', 'B', 'C'],
        'value1': [1, 2, 3]
    })
    
    df_right = pd.DataFrame({
        'key': ['A', 'B', 'D'],
        'value2': [4, 5, 6]
    })
    
    print(f"左表:\n{df_left}")
    print(f"\n右表:\n{df_right}")
    
    # 不同类型的连接
    inner_join = pd.merge(df_left, df_right, on='key', how='inner')
    left_join = pd.merge(df_left, df_right, on='key', how='left')
    outer_join = pd.merge(df_left, df_right, on='key', how='outer')
    
    print(f"\n内连接:\n{inner_join}")
    print(f"\n左连接:\n{left_join}")
    print(f"\n外连接:\n{outer_join}")
    
    # 3.3 自定义函数应用
    print("\n3.3 自定义函数应用:")
    
    # 创建示例数据
    df_func = pd.DataFrame({
        'A': [1, 2, 3, 4, 5],
        'B': [10, 20, 30, 40, 50],
        'C': [100, 200, 300, 400, 500]
    })
    
    print(f"原始数据:\n{df_func}")
    
    # 应用函数到列
    df_func['A_squared'] = df_func['A'].apply(lambda x: x**2)
    
    # 应用函数到行
    df_func['Row_sum'] = df_func[['A', 'B', 'C']].apply(sum, axis=1)
    
    # 应用自定义函数
    def categorize_value(x):
        if x < 100:
            return 'Low'
        elif x < 300:
            return 'Medium'
        else:
            return 'High'
    
    df_func['C_category'] = df_func['C'].apply(categorize_value)
    
    print(f"\n应用函数后:\n{df_func}")

# ============================================================================
# 第四部分：真实数据集应用 (Real-world Applications)
# ============================================================================

def real_world_applications():
    """真实数据集应用示例"""
    print("\n" + "=" * 60)
    print("第四部分：真实数据集应用")
    print("=" * 60)
    
    # 4.1 电商销售数据分析
    print("\n4.1 电商销售数据分析:")
    
    # 生成模拟电商数据
    np.random.seed(42)
    n_records = 1000
    
    # 生成日期范围
    start_date = datetime(2023, 1, 1)
    end_date = datetime(2023, 12, 31)
    date_range = pd.date_range(start_date, end_date, freq='D')
    
    ecommerce_data = {
        'order_id': range(1, n_records + 1),
        'order_date': np.random.choice(date_range, n_records),
        'customer_id': np.random.randint(1, 201, n_records),
        'product_category': np.random.choice(
            ['Electronics', 'Clothing', 'Books', 'Home', 'Sports'], n_records
        ),
        'product_price': np.random.uniform(10, 500, n_records).round(2),
        'quantity': np.random.randint(1, 6, n_records),
        'discount_rate': np.random.uniform(0, 0.3, n_records).round(2)
    }
    
    df_ecommerce = pd.DataFrame(ecommerce_data)
    df_ecommerce['total_amount'] = (
        df_ecommerce['product_price'] * 
        df_ecommerce['quantity'] * 
        (1 - df_ecommerce['discount_rate'])
    ).round(2)
    
    print(f"电商数据样本 (前5行):\n{df_ecommerce.head()}")
    print(f"\n数据基本信息:")
    print(f"  总订单数: {len(df_ecommerce):,}")
    print(f"  总销售额: ${df_ecommerce['total_amount'].sum():,.2f}")
    print(f"  平均订单金额: ${df_ecommerce['total_amount'].mean():.2f}")
    
    # 按类别分析
    category_analysis = df_ecommerce.groupby('product_category').agg({
        'order_id': 'count',
        'total_amount': ['sum', 'mean'],
        'quantity': 'sum'
    }).round(2)
    
    print(f"\n按产品类别分析:\n{category_analysis}")
    
    # 月度销售趋势
    df_ecommerce['order_month'] = df_ecommerce['order_date'].dt.to_period('M')
    monthly_sales = df_ecommerce.groupby('order_month')['total_amount'].sum()
    
    print(f"\n月度销售趋势 (前6个月):\n{monthly_sales.head(6)}")
    
    # 4.2 学生成绩数据分析
    print("\n4.2 学生成绩数据分析:")
    
    # 生成模拟学生成绩数据
    np.random.seed(123)
    n_students = 500
    
    student_data = {
        'student_id': range(1, n_students + 1),
        'name': [f'Student_{i}' for i in range(1, n_students + 1)],
        'grade': np.random.choice(['9', '10', '11', '12'], n_students),
        'math_score': np.random.normal(75, 15, n_students).clip(0, 100).round(1),
        'english_score': np.random.normal(78, 12, n_students).clip(0, 100).round(1),
        'science_score': np.random.normal(72, 18, n_students).clip(0, 100).round(1),
        'attendance_rate': np.random.uniform(0.7, 1.0, n_students).round(3)
    }
    
    df_students = pd.DataFrame(student_data)
    
    # 计算总分和平均分
    df_students['total_score'] = (
        df_students['math_score'] + 
        df_students['english_score'] + 
        df_students['science_score']
    ).round(1)
    
    df_students['average_score'] = (df_students['total_score'] / 3).round(1)
    
    # 成绩等级分类
    def grade_classification(score):
        if score >= 90:
            return 'A'
        elif score >= 80:
            return 'B'
        elif score >= 70:
            return 'C'
        elif score >= 60:
            return 'D'
        else:
            return 'F'
    
    df_students['grade_level'] = df_students['average_score'].apply(grade_classification)
    
    print(f"学生成绩数据样本 (前5行):\n{df_students.head()}")
    
    # 成绩统计分析
    print(f"\n成绩统计分析:")
    print(f"  数学平均分: {df_students['math_score'].mean():.1f}")
    print(f"  英语平均分: {df_students['english_score'].mean():.1f}")
    print(f"  科学平均分: {df_students['science_score'].mean():.1f}")
    
    # 等级分布
    grade_distribution = df_students['grade_level'].value_counts().sort_index()
    print(f"\n成绩等级分布:\n{grade_distribution}")
    
    # 年级对比
    grade_comparison = df_students.groupby('grade')[['math_score', 'english_score', 'science_score']].mean().round(1)
    print(f"\n各年级平均成绩对比:\n{grade_comparison}")
    
    # 4.3 股票市场数据分析
    print("\n4.3 股票市场数据分析:")
    
    # 生成模拟股票数据
    np.random.seed(456)
    trading_days = 252  # 一年的交易日
    
    # 生成多只股票的价格数据
    stocks = ['AAPL', 'GOOGL', 'MSFT', 'AMZN', 'TSLA']
    stock_data = {}
    
    dates = pd.date_range('2023-01-01', periods=trading_days, freq='B')  # 工作日
    
    for stock in stocks:
        # 使用几何布朗运动模拟股价
        initial_price = np.random.uniform(100, 300)
        returns = np.random.normal(0.0005, 0.02, trading_days)
        prices = [initial_price]
        
        for ret in returns:
            new_price = prices[-1] * (1 + ret)
            prices.append(new_price)
        
        stock_data[stock] = prices[1:]  # 移除初始价格
    
    df_stocks = pd.DataFrame(stock_data, index=dates)
    
    print(f"股票价格数据 (前5行):\n{df_stocks.head()}")
    
    # 计算收益率
    returns_df = df_stocks.pct_change().dropna()
    
    print(f"\n股票统计信息:")
    for stock in stocks:
        start_price = df_stocks[stock].iloc[0]
        end_price = df_stocks[stock].iloc[-1]
        total_return = (end_price - start_price) / start_price * 100
        volatility = returns_df[stock].std() * np.sqrt(252) * 100  # 年化波动率
        
        print(f"  {stock}: 起始价格=${start_price:.2f}, 结束价格=${end_price:.2f}, "
              f"总收益率={total_return:.1f}%, 年化波动率={volatility:.1f}%")
    
    # 相关性分析
    correlation_matrix = returns_df.corr()
    print(f"\n股票收益率相关性矩阵:\n{correlation_matrix.round(3)}")

# ============================================================================
# 第五部分：性能优化和最佳实践 (Performance & Best Practices)
# ============================================================================

def performance_and_best_practices():
    """性能优化和最佳实践"""
    print("\n" + "=" * 60)
    print("第五部分：性能优化和最佳实践")
    print("=" * 60)
    
    # 5.1 向量化操作 vs 循环
    print("\n5.1 向量化操作 vs 循环:")
    
    # 创建大型DataFrame
    n = 100000
    df_large = pd.DataFrame({
        'A': np.random.randn(n),
        'B': np.random.randn(n),
        'C': np.random.randn(n)
    })
    
    # 方法1：使用循环
    import time
    start_time = time.time()
    result_loop = []
    for i in range(len(df_large)):
        result_loop.append(df_large.iloc[i]['A'] * df_large.iloc[i]['B'])
    loop_time = time.time() - start_time
    
    # 方法2：使用向量化
    start_time = time.time()
    result_vectorized = df_large['A'] * df_large['B']
    vectorized_time = time.time() - start_time
    
    print(f"数据量: {n:,} 行")
    print(f"循环方法耗时: {loop_time:.4f} 秒")
    print(f"向量化方法耗时: {vectorized_time:.4f} 秒")
    print(f"性能提升: {loop_time/vectorized_time:.1f}x")
    
    # 5.2 内存优化
    print("\n5.2 内存优化:")
    
    # 创建示例数据
    df_memory = pd.DataFrame({
        'int_col': np.random.randint(0, 100, 10000),
        'float_col': np.random.random(10000),
        'string_col': ['category_' + str(i % 10) for i in range(10000)]
    })
    
    print(f"原始内存使用:")
    print(df_memory.info(memory_usage='deep'))
    
    # 优化数据类型
    df_optimized = df_memory.copy()
    df_optimized['int_col'] = df_optimized['int_col'].astype('int8')
    df_optimized['float_col'] = df_optimized['float_col'].astype('float32')
    df_optimized['string_col'] = df_optimized['string_col'].astype('category')
    
    print(f"\n优化后内存使用:")
    print(df_optimized.info(memory_usage='deep'))
    
    # 5.3 常用技巧和陷阱
    print("\n5.3 常用技巧和陷阱:")
    
    # 技巧1：使用query方法进行复杂筛选
    df_tips = pd.DataFrame({
        'total_bill': [16.99, 10.34, 21.01, 23.68, 24.59],
        'tip': [1.01, 1.66, 3.50, 3.31, 3.61],
        'sex': ['Female', 'Male', 'Male', 'Male', 'Female'],
        'time': ['Dinner', 'Dinner', 'Dinner', 'Dinner', 'Dinner']
    })
    
    # 传统方法
    result1 = df_tips[(df_tips['total_bill'] > 20) & (df_tips['sex'] == 'Male')]
    
    # 使用query方法（更清晰）
    result2 = df_tips.query('total_bill > 20 and sex == "Male"')
    
    print(f"使用query方法筛选结果:\n{result2}")
    
    # 技巧2：链式操作
    result_chain = (df_tips
                   .query('total_bill > 15')
                   .assign(tip_rate=lambda x: x['tip'] / x['total_bill'])
                   .sort_values('tip_rate', ascending=False)
                   .head(3))
    
    print(f"\n链式操作结果:\n{result_chain}")
    
    # 技巧3：使用pipe进行自定义操作
    def add_tip_category(df):
        df['tip_category'] = pd.cut(df['tip'], bins=[0, 2, 3, 5], labels=['Low', 'Medium', 'High'])
        return df
    
    result_pipe = df_tips.pipe(add_tip_category)
    print(f"\n使用pipe添加分类:\n{result_pipe}")

def run_all_examples():
    """运行所有示例"""
    print("Pandas 从基础到高级完整示例")
    print("=" * 80)
    
    basic_pandas_operations()
    intermediate_pandas_operations()
    advanced_pandas_operations()
    real_world_applications()
    performance_and_best_practices()
    
    print("\n" + "=" * 80)
    print("所有示例运行完成！")
    print("=" * 80)

def run_tests():
    """运行测试"""
    print("\n" + "=" * 60)
    print("运行测试")
    print("=" * 60)
    
    # 测试Series创建
    s = pd.Series([1, 2, 3, 4, 5])
    assert len(s) == 5, "Series创建测试失败"
    assert s.sum() == 15, "Series求和测试失败"
    
    # 测试DataFrame创建
    df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
    assert df.shape == (3, 2), "DataFrame形状测试失败"
    assert list(df.columns) == ['A', 'B'], "DataFrame列名测试失败"
    
    # 测试数据选择
    result = df[df['A'] > 1]
    assert len(result) == 2, "条件选择测试失败"
    
    # 测试分组操作
    df_group = pd.DataFrame({
        'category': ['A', 'A', 'B', 'B'],
        'value': [1, 2, 3, 4]
    })
    grouped = df_group.groupby('category')['value'].sum()
    assert grouped['A'] == 3, "分组求和测试失败"
    assert grouped['B'] == 7, "分组求和测试失败"
    
    # 测试数据合并
    df1 = pd.DataFrame({'key': ['A', 'B'], 'value1': [1, 2]})
    df2 = pd.DataFrame({'key': ['A', 'B'], 'value2': [3, 4]})
    merged = pd.merge(df1, df2, on='key')
    assert merged.shape == (2, 3), "数据合并测试失败"
    
    # 测试时间序列
    dates = pd.date_range('2023-01-01', periods=5, freq='D')
    ts = pd.Series(range(5), index=dates)
    assert len(ts) == 5, "时间序列创建测试失败"
    
    print("✅ 所有测试通过！")

if __name__ == "__main__":
    run_all_examples()
    run_tests()